Laravel Database——Eloquent Model 模型关系加载与查询

前言

我们在上一篇文章中介绍了模型关系的定义初始化,我们可以看到,在初始化的过程中 laravel 已经为各种关联关系的模型预先插入了初始的 where 条件。本文将会进一步介绍如何添加自定义的查询条件,如何加载、预加载关联模型。

关联模型的加载

当我们定义关联模型后:

  1. class User extends Model
  2. {
  3. /**
  4. * 获得与用户关联的电话记录。
  5. */
  6. public function phone()
  7. {
  8. $this->hasOne('App\Phone', 'user_id', 'id');
  9. }
  10. }

我们可以像成员变量一样来获取与之关联的模型:

  1. $user = App\User::find(1);
  2. foreach ($user->posts as $post) {
  3. //
  4. }

实际上,模型的属性获取函数的确可以加载关联模型:

  1. public function getAttribute($key)
  2. {
  3. if (! $key) {
  4. return;
  5. }
  6. ...
  7. return $this->getRelationValue($key);
  8. }

getRelationValue 函数专用于加载我们之前定义的关联模型:

  1. public function getRelationValue($key)
  2. {
  3. if ($this->relationLoaded($key)) {
  4. return $this->relations[$key];
  5. }
  6. if (method_exists($this, $key)) {
  7. return $this->getRelationshipFromMethod($key);
  8. }
  9. }
  10. public function relationLoaded($key)
  11. {
  12. return array_key_exists($key, $this->relations);
  13. }

可以看到,关联的加载带有缓存,laravel 首先会验证当前关联关系是否已经被加载,如果加载过,那么直接返回缓存结果。

  1. protected function getRelationshipFromMethod($method)
  2. {
  3. $relation = $this->$method();
  4. if (! $relation instanceof Relation) {
  5. throw new LogicException(get_class($this).'::'.$method.' must return a relationship instance.');
  6. }
  7. return tap($relation->getResults(), function ($results) use ($method) {
  8. $this->setRelation($method, $results);
  9. });
  10. }

当我们调用 $user->posts 语句的时候,laravel 会调用 posts 函数,该函数开始定义关联关系,并且返回 hasOne 对象,在这里将会调用 getResults 函数来加载关联模型:

  1. public function getResults()
  2. {
  3. return $this->query->first() ?: $this->getDefaultFor($this->parent);
  4. }

getDefaultFor 函数用于在未查询到任何关联模型时的情况。我们在定义关联的时候,可以提供默认的方法来控制返回的结果:

  1. public function user()
  2. {
  3. return $this->belongsTo('App\User')->withDefault();
  4. }
  5. public function user()
  6. {
  7. return $this->belongsTo('App\User')->withDefault([
  8. 'name' => '游客',
  9. ]);
  10. }
  11. public function user()
  12. {
  13. return $this->belongsTo('App\User')->withDefault(function ($user) {
  14. $user->name = '游客';
  15. });
  16. }

withDefault 可以提供空值、数组、闭包函数等等选项,getDefaultFor 函数在关联没有查询到结果的时候,按要求返回一个模型:

  1. public function withDefault($callback = true)
  2. {
  3. $this->withDefault = $callback;
  4. return $this;
  5. }
  6. protected function getDefaultFor(Model $parent)
  7. {
  8. if (! $this->withDefault) {
  9. return;
  10. }
  11. $instance = $this->newRelatedInstanceFor($parent);
  12. if (is_callable($this->withDefault)) {
  13. return call_user_func($this->withDefault, $instance) ?: $instance;
  14. }
  15. if (is_array($this->withDefault)) {
  16. $instance->forceFill($this->withDefault);
  17. }
  18. return $instance;
  19. }

获取到关联模型后,就要放入缓存当中,以备后续情况使用:

  1. public function setRelation($relation, $value)
  2. {
  3. $this->relations[$relation] = $value;
  4. return $this;
  5. }

多对多关系的加载

多对多关系的加载与一对多等关系的加载有所不同,原因是不仅要加载 related 模型,还要加载中间表模型:

  1. public function getResults()
  2. {
  3. return $this->get();
  4. }
  5. public function get($columns = ['*'])
  6. {
  7. $columns = $this->query->getQuery()->columns ? [] : $columns;
  8. $builder = $this->query->applyScopes();
  9. $models = $builder->addSelect(
  10. $this->shouldSelect($columns)
  11. )->getModels();
  12. $this->hydratePivotRelation($models);
  13. if (count($models) > 0) {
  14. $models = $builder->eagerLoadRelations($models);
  15. }
  16. return $this->related->newCollection($models);
  17. }

shouldSelect 函数加载了中间表的字段属性:

  1. protected function shouldSelect(array $columns = ['*'])
  2. {
  3. if ($columns == ['*']) {
  4. $columns = [$this->related->getTable().'.*'];
  5. }
  6. return array_merge($columns, $this->aliasedPivotColumns());
  7. }
  8. protected function aliasedPivotColumns()
  9. {
  10. $defaults = [$this->foreignPivotKey, $this->relatedPivotKey];
  11. return collect(array_merge($defaults, $this->pivotColumns))->map(function ($column) {
  12. return $this->table.'.'.$column.' as pivot_'.$column;
  13. })->unique()->all();
  14. }

可以看到,这个时候,中间表的属性会被放入 related 模型中,并且会被赋予别名前缀 pivot_

接着 hydratePivotRelation 会将这些中间表属性加载到中间表模型中:

  1. protected function hydratePivotRelation(array $models)
  2. {
  3. foreach ($models as $model) {
  4. $model->setRelation($this->accessor, $this->newExistingPivot(
  5. $this->migratePivotAttributes($model)
  6. ));
  7. }
  8. }
  9. protected function migratePivotAttributes(Model $model)
  10. {
  11. $values = [];
  12. foreach ($model->getAttributes() as $key => $value) {
  13. if (strpos($key, 'pivot_') === 0) {
  14. $values[substr($key, 6)] = $value;
  15. unset($model->$key);
  16. }
  17. }
  18. return $values;
  19. }

accessor 默认值为 pivot,我们也可以在定义多对多的时候使用 as 函数为它取别名:

  1. return $this->belongsToMany('App\Role')->as(‘role_user’);

源码:

  1. public function as($accessor)
  2. {
  3. $this->accessor = $accessor;
  4. return $this;
  5. }

关联模型的预加载

with 函数

当作为属性访问 Eloquent 关联时,关联数据是「懒加载」的。意味着在你第一次访问该属性时,才会加载关联数据。不过,当你查询父模型时,Eloquent 还可以进行「预加载」关联数据。预加载避免了 N + 1 查询问题。

预加载可以一次操作中预加载关联模型并且自定义用于 select 的列,可以预加载几个不同的关联,还可以预加载嵌套关联,预加载关联数据的时候,为查询指定额外的约束条件:

  1. $books = App\Book::with(['author:id,name'])->get();
  2. $books = App\Book::with(['author', 'publisher'])->get();
  3. $books = App\Book::with('author.contacts')->get();
  4. $users = App\User::with(['posts' => function ($query) {
  5. $query->where('title', 'like', '%first%');
  6. }])->get();

我们来看看 with 函数:

  1. public static function with($relations)
  2. {
  3. return (new static)->newQuery()->with(
  4. is_string($relations) ? func_get_args() : $relations
  5. );
  6. }

预加载调用 Eloquent/builderwith 函数:

  1. public function with($relations)
  2. {
  3. $eagerLoad = $this->parseWithRelations(is_string($relations) ? func_get_args() : $relations);
  4. $this->eagerLoad = array_merge($this->eagerLoad, $eagerLoad);
  5. return $this;
  6. }

eagerLoad 成员变量用于存放预加载的关联关系,parseWithRelations 用于解析关联关系:

  1. protected function parseWithRelations(array $relations)
  2. {
  3. $results = [];
  4. foreach ($relations as $name => $constraints) {
  5. if (is_numeric($name)) {
  6. $name = $constraints;
  7. list($name, $constraints) = Str::contains($name, ':')
  8. ? $this->createSelectWithConstraint($name)
  9. : [$name, function () {
  10. //
  11. }];
  12. }
  13. $results = $this->addNestedWiths($name, $results);
  14. $results[$name] = $constraints;
  15. }
  16. return $results;
  17. }

当我们在模型关系中写入 : 符合的时候,说明我们不想 select *,而是想要只查询特定的字段,createSelectWithConstraint:

  1. protected function createSelectWithConstraint($name)
  2. {
  3. return [explode(':', $name)[0], function ($query) use ($name) {
  4. $query->select(explode(',', explode(':', $name)[1]));
  5. }];
  6. }

也就是为关联关系添加 select 条件。

当我们想要进行嵌套查询的时候,需要在关联关系中写下 .addNestedWiths :

  1. protected function addNestedWiths($name, $results)
  2. {
  3. $progress = [];
  4. foreach (explode('.', $name) as $segment) {
  5. $progress[] = $segment;
  6. if (! isset($results[$last = implode('.', $progress)])) {
  7. $results[$last] = function () {
  8. //
  9. };
  10. }
  11. }
  12. return $results;
  13. }

可以看到,addNestedWiths 为嵌套的模型关系赋予默认的空闭包函数,例如 a.b.caddNestedWiths 返回的 results 数组中会有三个成员: aa.ba.b.c,这三个变量的闭包函数都是空。

接下来,parseWithRelationsa.b.c 的闭包函数重新赋值,将用户定义的约束条件赋予给 a.b.c

get 函数预加载

with 函数为 laravel 提供了需要预加载的关联关系,get 函数在从数据库中获取父模型的数据后,会将需要预加载的模型也一并取出来:

  1. public function get($columns = ['*'])
  2. {
  3. $builder = $this->applyScopes();
  4. if (count($models = $builder->getModels($columns)) > 0) {
  5. $models = $builder->eagerLoadRelations($models);
  6. }
  7. return $builder->getModel()->newCollection($models);
  8. }

顾名思义 eagerLoadRelations 函数就是获取预加载模型的的函数:

  1. public function eagerLoadRelations(array $models)
  2. {
  3. foreach ($this->eagerLoad as $name => $constraints) {
  4. // For nested eager loads we'll skip loading them here and they will be set as an
  5. // eager load on the query to retrieve the relation so that they will be eager
  6. // loaded on that query, because that is where they get hydrated as models.
  7. if (strpos($name, '.') === false) {
  8. $models = $this->eagerLoadRelation($models, $name, $constraints);
  9. }
  10. }
  11. return $models;
  12. }

在这里,很让人费解的是 if 条件,这个条件语句看起来排除了嵌套预加载关系。例如上面的 a.b.ceagerLoadRelations 只会加载 a 这个关联关系。其实原因是:

// For nested eager loads we’ll skip loading them here and they will be set as an
eager load on the query to retrieve the relation so that they will be eager
loaded on that query, because that is where they get hydrated as models.

翻译过来就是说,嵌套预加载要一步一步的来,第一次只加载 a,获得了 a 的关联模型之后,第二次再加载 b,最后加载 c。这里看不懂没关系,答案在下面的代码中:

  1. protected function eagerLoadRelation(array $models, $name, Closure $constraints)
  2. {
  3. $relation = $this->getRelation($name);
  4. $relation->addEagerConstraints($models);
  5. $constraints($relation);
  6. return $relation->match(
  7. $relation->initRelation($models, $name),
  8. $relation->getEager(), $name
  9. );
  10. }

eagerLoadRelation 是预加载关联关系的核心,我们可以看到加载关联模型关系主要有四个步骤:

  • 通过关系名来调用 hasOne 等函数来加载模型关系 relation
  • 利用 models 来为模型关系添加约束条件
  • 调用 with 函数附带的约束条件
  • 从数据库获取关联模型并匹配到各个父模型中,作为父模型的属性

我们先从调用关联函数 getRelation 来说:

getRelation

  1. public function getRelation($name)
  2. {
  3. $relation = Relation::noConstraints(function () use ($name) {
  4. try {
  5. return $this->getModel()->{$name}();
  6. } catch (BadMethodCallException $e) {
  7. throw RelationNotFoundException::make($this->getModel(), $name);
  8. }
  9. });
  10. $nested = $this->relationsNestedUnder($name);
  11. if (count($nested) > 0) {
  12. $relation->getQuery()->with($nested);
  13. }
  14. return $relation;
  15. }

我们在上一个文章说过,hasOne 等函数会自动加约束条件例如:

  1. select phone where phone.user_id = user.id

但是这个约束条件并不适用于预加载,因为预加载的父模型通常不只只一个。因此需要调用函数 noConstraints 来避免加载约束条件:

  1. public static function noConstraints(Closure $callback)
  2. {
  3. $previous = static::$constraints;
  4. static::$constraints = false;
  5. try {
  6. return call_user_func($callback);
  7. } finally {
  8. static::$constraints = $previous;
  9. }
  10. }

接下来,就要调用定义关联的函数:

  1. return $this->getModel()->{$name}();

下面的 relationsNestedUnder 函数用于加载嵌套的预加载关联关系:

  1. protected function relationsNestedUnder($relation)
  2. {
  3. $nested = [];
  4. foreach ($this->eagerLoad as $name => $constraints) {
  5. if ($this->isNestedUnder($relation, $name)) {
  6. $nested[substr($name, strlen($relation.'.'))] = $constraints;
  7. }
  8. }
  9. return $nested;
  10. }
  11. protected function isNestedUnder($relation, $name)
  12. {
  13. return Str::contains($name, '.') && Str::startsWith($name, $relation.'.');
  14. }

从代码上可以看出来,如果当前的模型关系是 arelationsNestedUnder 函数会把其嵌套的关系都检测出来:a.ba.b.c,并且放入 nested 数组中:nested[b]、nested[b.c]

接下来:

  1. if (count($nested) > 0) {
  2. $relation->getQuery()->with($nested);
  3. }

就会继续递归预加载关联关系。

关联关系预加载约束条件

获得关联关系之后,就要加载各个关联关系自己的预加载约束条件:

  1. public function addEagerConstraints(array $models)
  2. {
  3. $this->query->whereIn(
  4. $this->foreignKey, $this->getKeys($models, $this->localKey)
  5. );
  6. }

也就是从父模型的外键来为关联模型添加 where 条件。当然各个关联关系不同,这个函数也有一定的区别。

with 预加载约束条件

接下来还有加载 with 函数的约束条件 :

  1. $constraints($relation);

匹配父模型

当关联关系的约束条件都设置完毕后,就要从数据库中来获取关联模型:

  1. $relation->match(
  2. $relation->initRelation($models, $name),
  3. $relation->getEager(), $name
  4. );
  5. public function getEager()
  6. {
  7. return $this->get();
  8. }

initRelation 会为父模型设置默认的关联模型:

  1. public function initRelation(array $models, $relation)
  2. {
  3. foreach ($models as $model) {
  4. $model->setRelation($relation, $this->getDefaultFor($model));
  5. }
  6. return $models;
  7. }

两步都做好了,接下来就要为父模型和子模型进行匹配了:

  1. public function match(array $models, Collection $results, $relation)
  2. {
  3. return $this->matchOne($models, $results, $relation);
  4. }
  5. public function matchOne(array $models, Collection $results, $relation)
  6. {
  7. return $this->matchOneOrMany($models, $results, $relation, 'one');
  8. }
  9. protected function matchOneOrMany(array $models, Collection $results, $relation, $type)
  10. {
  11. $dictionary = $this->buildDictionary($results);
  12. foreach ($models as $model) {
  13. if (isset($dictionary[$key = $model->getAttribute($this->localKey)])) {
  14. $model->setRelation(
  15. $relation, $this->getRelationValue($dictionary, $key, $type)
  16. );
  17. }
  18. }
  19. return $models;
  20. }

匹配的过程分为两步:创建目录 buildDictionary 和设置子模型 setRelation

  1. protected function buildDictionary(Collection $results)
  2. {
  3. $dictionary = [];
  4. $foreign = $this->getForeignKeyName();
  5. foreach ($results as $result) {
  6. $dictionary[$result->{$foreign}][] = $result;
  7. }
  8. return $dictionary;
  9. }

创建目录 buildDictionary 函数根据子模型的外键 foreign 将子模型进行分类,拥有同一外键的子模型放入同一个数组中。

接下来,为父模型设置子模型:

  1. foreach ($models as $model) {
  2. if (isset($dictionary[$key = $model->getAttribute($this->localKey)])) {
  3. $model->setRelation(
  4. $relation, $this->getRelationValue($dictionary, $key, $type)
  5. );
  6. }
  7. }
  8. protected function getRelationValue(array $dictionary, $key, $type)
  9. {
  10. $value = $dictionary[$key];
  11. return $type == 'one' ? reset($value) : $this->related->newCollection($value);
  12. }

如果目录 dictionary 中存在父模型的主键,就会从目录中取出对应的子模型数组,并利用 setRelation 来为父模型设置关联模型。

关联模型的关联查询

基于存在的关联查询

官方样例:

  1. // 获得所有至少有一条评论的文章...
  2. $posts = App\Post::has('comments')->get();
  3. // 获得所有有三条或三条以上评论的文章...
  4. $posts = Post::has('comments', '>=', 3)->get();
  5. // 获得所有至少有一条获赞评论的文章...
  6. $posts = Post::has('comments.votes')->get();
  7. // 获得所有至少有一条评论内容满足 foo% 条件的文章
  8. $posts = Post::whereHas('comments', function ($query) {
  9. $query->where('content', 'like', 'foo%');
  10. })->get();

has 函数用于基于存在的关联查询:

  1. public function has($relation, $operator = '>=', $count = 1, $boolean = 'and', Closure $callback = null)
  2. {
  3. if (strpos($relation, '.') !== false) {
  4. return $this->hasNested($relation, $operator, $count, $boolean, $callback);
  5. }
  6. $relation = $this->getRelationWithoutConstraints($relation);
  7. $method = $this->canUseExistsForExistenceCheck($operator, $count)
  8. ? 'getRelationExistenceQuery'
  9. : 'getRelationExistenceCountQuery';
  10. $hasQuery = $relation->{$method}(
  11. $relation->getRelated()->newQuery(), $this
  12. );
  13. if ($callback) {
  14. $hasQuery->callScope($callback);
  15. }
  16. return $this->addHasWhere(
  17. $hasQuery, $relation, $operator, $count, $boolean
  18. );
  19. }

has 函数的步骤:

  • 获取无约束的关联关系
  • 为关联关系添加 existence 约束
  • 为关联关系添加 has 外部约束
  • 将关联关系添加到 where 条件中

无约束的关联关系

  1. protected function getRelationWithoutConstraints($relation)
  2. {
  3. return Relation::noConstraints(function () use ($relation) {
  4. return $this->getModel()->{$relation}();
  5. });
  6. }

这个不用多说,和预加载的原理一样。

existence 约束

关系模型的 existence 约束条件很简单:

  1. select * from post where user.id = post.user_id

laravel 还考虑一种特殊情况,那就是自己关联自己,这个时候就会为模型命名一个新的 hash

  1. select * from user as wedfklk where user.id = wedfklk.foreignKey

源代码比较简单:

  1. public function getRelationExistenceQuery(Builder $query, Builder $parentQuery, $columns = ['*'])
  2. {
  3. if ($query->getQuery()->from == $parentQuery->getQuery()->from) {
  4. return $this->getRelationExistenceQueryForSelfRelation($query, $parentQuery, $columns);
  5. }
  6. return parent::getRelationExistenceQuery($query, $parentQuery, $columns);
  7. }
  8. public function getRelationExistenceQueryForSelfRelation(Builder $query, Builder $parentQuery, $columns = ['*'])
  9. {
  10. $query->from($query->getModel()->getTable().' as '.$hash = $this->getRelationCountHash());
  11. $query->getModel()->setTable($hash);
  12. return $query->select($columns)->whereColumn(
  13. $this->getQualifiedParentKeyName(), '=', $hash.'.'.$this->getForeignKeyName()
  14. );
  15. }
  16. public function getRelationExistenceQuery(Builder $query, Builder $parentQuery, $columns = ['*'])
  17. {
  18. return $query->select($columns)->whereColumn(
  19. $this->getQualifiedParentKeyName(), '=', $this->getExistenceCompareKey()
  20. );
  21. }
  22. public function getExistenceCompareKey()
  23. {
  24. return $this->getQualifiedForeignKeyName();
  25. }

ExistenceCount 约束

ExistenceCount 约束只是 select * 变成了 select count(*):

  1. select count(*) from post where user.id = post.user_id

源代码:

  1. public function getRelationExistenceCountQuery(Builder $query, Builder $parentQuery)
  2. {
  3. return $this->getRelationExistenceQuery(
  4. $query, $parentQuery, new Expression('count(*)')
  5. );
  6. }

关联关系添加到 where 条件

当关联关系的 存在 约束设置完毕后,就要加载到父模型的 where 条件中,一般会作为父模型的子查询:

  1. protected function addHasWhere(Builder $hasQuery, Relation $relation, $operator, $count, $boolean)
  2. {
  3. $hasQuery->mergeConstraintsFrom($relation->getQuery());
  4. return $this->canUseExistsForExistenceCheck($operator, $count)
  5. ? $this->addWhereExistsQuery($hasQuery->toBase(), $boolean, $operator === '<' && $count === 1)
  6. : $this->addWhereCountQuery($hasQuery->toBase(), $operator, $count, $boolean);
  7. }
  8. public function addWhereExistsQuery(Builder $query, $boolean = 'and', $not = false)
  9. {
  10. $type = $not ? 'NotExists' : 'Exists';
  11. $this->wheres[] = compact('type', 'operator', 'query', 'boolean');
  12. $this->addBinding($query->getBindings(), 'where');
  13. return $this;
  14. }
  15. protected function addWhereCountQuery(QueryBuilder $query, $operator = '>=', $count = 1, $boolean = 'and')
  16. {
  17. $this->query->addBinding($query->getBindings(), 'where');
  18. return $this->where(
  19. new Expression('('.$query->toSql().')'),
  20. $operator,
  21. is_numeric($count) ? new Expression($count) : $count,
  22. $boolean
  23. );
  24. }

existence 约束最后条件:

  1. select * from user where exists (select * from phone where phone.user_id=user.id)

ExistenceCount 约束:

  1. select * from user where (select count(*) from phone where phone.user_id=user.id) >= 3

嵌套查询

嵌套查询需要进行递归来调用 has 函数:

  1. protected function hasNested($relations, $operator = '>=', $count = 1, $boolean = 'and', $callback = null)
  2. {
  3. $relations = explode('.', $relations);
  4. $closure = function ($q) use (&$closure, &$relations, $operator, $count, $callback) {
  5. count($relations) > 1
  6. ? $q->whereHas(array_shift($relations), $closure)
  7. : $q->has(array_shift($relations), $operator, $count, 'and', $callback);
  8. };
  9. return $this->has(array_shift($relations), '>=', 1, $boolean, $closure);
  10. }
  11. public function whereHas($relation, Closure $callback = null, $operator = '>=', $count = 1)
  12. {
  13. return $this->has($relation, $operator, $count, 'and', $callback);
  14. }

例如

  1. $posts = Post::has('comments.votes')->get();

首先 hasNested 会返回:

  1. $this->has('comments', '>=', 1, 'and', function ($q) use (&$closure, votes’, '>=', 1, $callback) {
  2. $q->has(‘votes’, '>=', 1, 'and', $callback);
  3. }
  4. );

生成的 sql:

  1. select * from post where exist (select * from comment where comment.post_id=post.id and where exist (select * from vote where vote.comment_id=comment.id))

基于不存在的关联查询

基于不存在的关联查询只是基于存在的关联查询

  1. public function doesntHave($relation, $boolean = 'and', Closure $callback = null)
  2. {
  3. return $this->has($relation, '<', 1, $boolean, $callback);
  4. }
  5. public function whereDoesntHave($relation, Closure $callback = null)
  6. {
  7. return $this->doesntHave($relation, 'and', $callback);
  8. }

关联数据计数

如果您只想统计结果数而不需要加载实际数据,那么可以使用 withCount 方法,此方法会在您的结果集模型中添加一个 {关联名}_count 字段。例如:

  1. $posts = App\Post::withCount('comments')->get();
  2. //select *,(select count(*) from comment where comment.post_id=post.id) as comments_count from post
  3. foreach ($posts as $post) {
  4. echo $post->comments_count;
  5. }
  6. //多个关联数据「计数」,并为其查询添加约束条件:
  7. $posts = Post::withCount(['votes', 'comments' => function ($query) {
  8. $query->where('content', 'like', 'foo%');
  9. }])->get();
  10. //select *,(select count(*) from comment where comment.post_id=post.id and content like 'foo%') as comments_count,(select count(*) from votes where vote.post_id=post.id) as votes_count from post
  11. echo $posts[0]->votes_count;
  12. echo $posts[0]->comments_count;
  13. //可以为关联数据计数结果起别名,允许在同一个关联上多次计数:
  14. $posts = Post::withCount([
  15. 'comments',
  16. 'comments as pending_comments_count' => function ($query) {
  17. $query->where('approved', false);
  18. }
  19. ])->get();
  20. //select *,(select count(*) from comment where comment.post_id=post.id) as comments_count,(select count(*) from comment where comment.post_id=post.id and approved=false) as pending_comments_count from post
  21. echo $posts[0]->comments_count;
  22. echo $posts[0]->pending_comments_count;

withCount 的源代码与 has 的代码高度相似:

  1. public function withCount($relations)
  2. {
  3. if (empty($relations)) {
  4. return $this;
  5. }
  6. if (is_null($this->query->columns)) {
  7. $this->query->select([$this->query->from.'.*']);
  8. }
  9. $relations = is_array($relations) ? $relations : func_get_args();
  10. foreach ($this->parseWithRelations($relations) as $name => $constraints) {
  11. $segments = explode(' ', $name);
  12. unset($alias);
  13. if (count($segments) == 3 && Str::lower($segments[1]) == 'as') {
  14. list($name, $alias) = [$segments[0], $segments[2]];
  15. }
  16. $relation = $this->getRelationWithoutConstraints($name);
  17. $query = $relation->getRelationExistenceCountQuery(
  18. $relation->getRelated()->newQuery(), $this
  19. );
  20. $query->callScope($constraints);
  21. $query->mergeConstraintsFrom($relation->getQuery());
  22. $column = $alias ?? Str::snake($name.'_count');
  23. $this->selectSub($query->toBase(), $column);
  24. }
  25. return $this;
  26. }
  • 解析关联关系名称
  • 获取无约束的关联关系
  • 为关联关系添加 existenceCount 约束
  • 为关联关系添加 with 外部约束
  • 将关联关系添加到 where 条件中
  • 设置 alias 别名
  • 创建 select 子查询

多对多关系的中间表查询

  1. return $this->belongsToMany('App\Role')->wherePivot('approved', 1);
  2. return $this->belongsToMany('App\Role')->wherePivotIn('priority', [1, 2]);
  1. public function wherePivot($column, $operator = null, $value = null, $boolean = 'and')
  2. {
  3. $this->pivotWheres[] = func_get_args();
  4. return $this->where($this->table.'.'.$column, $operator, $value, $boolean);
  5. }
  6. public function wherePivotIn($column, $values, $boolean = 'and', $not = false)
  7. {
  8. $this->pivotWhereIns[] = func_get_args();
  9. return $this->whereIn($this->table.'.'.$column, $values, $boolean, $not);
  10. }

注意这里的 pivotWherespivotWheres 变量,这个变量在对中间表的加载中会被使用:

  1. protected function newPivotQuery()
  2. {
  3. $query = $this->newPivotStatement();
  4. foreach ($this->pivotWheres as $arguments) {
  5. call_user_func_array([$query, 'where'], $arguments);
  6. }
  7. foreach ($this->pivotWhereIns as $arguments) {
  8. call_user_func_array([$query, 'whereIn'], $arguments);
  9. }
  10. return $query->where($this->foreignPivotKey, $this->parent->{$this->parentKey});
  11. }